import { createPool, Pool } from 'mysql2/promise'

type ConnConfig = {
  host: string
  port?: number
  user: string
  password?: string
  database: string
  connectionLimit?: number
}

function parseConn(): ConnConfig | null {
  const host = process.env.MYSQL_HOST
  const port = process.env.MYSQL_PORT ? Number(process.env.MYSQL_PORT) : undefined
  const user = process.env.MYSQL_USER
  const password = process.env.MYSQL_PASSWORD
  const database = process.env.MYSQL_DATABASE
  const connectionLimit = process.env.MYSQL_CONN_LIMIT ? Number(process.env.MYSQL_CONN_LIMIT) : undefined

  if (host && user && database) return { host, port, user, password, database, connectionLimit }

  const raw = process.env.MYSQL_CONN || process.env.DATABASE_CONN || process.env.MYSQL_CONNECTION_STRING || ''
  if (!raw) return null

  const parts = raw.split(';').map(s => s.trim()).filter(Boolean)
  const map: Record<string, string> = {}
  for (const p of parts) {
    const idx = p.indexOf('=')
    if (idx === -1) continue
    const k = p.slice(0, idx).trim().toLowerCase()
    const v = p.slice(idx + 1).trim()
    map[k] = v
  }

  const hostVal = map['data source'] || map['data_source'] || map['server'] || map['host'] || map['datasource']
  const portVal = map['port'] ? Number(map['port']) : undefined
  const userVal = map['user id'] || map['user'] || map['uid'] || map['userid']
  const passwordVal = map['password'] || map['pwd']
  const databaseVal = map['initial catalog'] || map['database'] || map['initial_catalog']
  const maxPool = map['max pool size'] ? Number(map['max pool size']) : undefined

  if (!hostVal || !userVal || !databaseVal) return null

  return {
    host: hostVal,
    port: portVal,
    user: userVal,
    password: passwordVal,
    database: databaseVal,
    connectionLimit: maxPool
  }
}

let cachedPool: Pool | null = null

export async function getPool(): Promise<Pool> {
  if (cachedPool) return cachedPool
  const cfg = parseConn()
  if (!cfg) throw new Error('MySQL connection not configured. Set MYSQL_HOST/MYSQL_USER/MYSQL_DATABASE or MYSQL_CONN environment variable.')

  const pool = createPool({
    host: cfg.host,
    port: cfg.port,
    user: cfg.user,
    password: cfg.password,
    database: cfg.database,
    waitForConnections: true,
    connectionLimit: cfg.connectionLimit || 3,
    charset: 'utf8mb4',
    // 使用上海时区 (+08:00)。mysql2 支持 'Z' 或偏移字符串（例如 '+08:00'）
    // 这样在驱动层面解析/序列化 DATETIME/TIMESTAMP 时会以该时区处理。
    timezone: '+08:00'
  })

  cachedPool = pool
  return pool
}

export async function query<T = any>(sql: string, params?: any[]) {
  const pool = await getPool()
  // mysql2 的 execute 返回 [rows, fields]，为了避免复杂的类型约束，这里使用类型断言
  return pool.execute(sql, params || []) as Promise<[T[], any]>
}

export default { getPool, query }
